﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml.Serialization;
using Cinser.Common;
using CExcelOp;
using System.Windows.Forms;
using System.Data;
using CExcelOp.BLL;
using Cinser.Common.Wpf.Controls;
using Cinser.Common.Wpf;
namespace CExcelOp
{
    public enum EditState
    {
        New,
        Edit
    };
    public delegate DataTable ExportHandler(string configFileName);
    public delegate bool FieldValidateHandler(DataRow dr,out string errInfo);
    public delegate void OperationAfterSave(DataRow data,EditState editState);
    public delegate void OperationBeforeSave(DataRow data, EditState editState);
    public delegate void OperationAfterExport(DataRow data);
    public delegate void OperationBeforeValidate(DataTable dt);
    public delegate string ConvertHandler(string ConvertField,DataRow dr);
    public delegate void ConvertNameToCodeHandler(DataTable dt, List<string> tableNames, List<string> FieldName);
    public delegate string CommonValidate(DataRow dr);
    
    // 固定单元格
    [Serializable]
    public class FixedCellTemplate
    {
        string rowIndex;   //行号从1开始
        public string RowIndex
        {
            get { return rowIndex; }
            set { rowIndex = value; }
        }
        string columnIndex;  //列号从1开始

        public string ColumnIndex
        {
            get { return columnIndex; }
            set { columnIndex = value; }
        }
        string fieldName;  //对应的数据库字段名字

        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }
        string allowNull = "1"; //是否允许空

        public string AllowNull
        {
            get { return allowNull; }
            set { allowNull = value; }
        }
        string bindTableName = ""; //下拉列表绑定的表名

        public string BindTableName
        {
            get { return bindTableName; }
            set { bindTableName = value; }
        }
        string bindTableChineseName = "";  //绑定表的中文名

        public string BindTableChineseName
        {
            get { return bindTableChineseName; }
            set { bindTableChineseName = value; }
        }
        string bindFieldName = "";//绑定的字段名(字典表不用写)

        public string BindFieldName
        {
            get { return bindFieldName; }
            set { bindFieldName = value; }
        }
        private string bindFieldChineseName = ""; //绑定字段的中文名

        public string BindFieldChineseName
        {
            get { return bindFieldChineseName; }
            set { bindFieldChineseName = value; }
        }
        private string isBGD = "1"; //BGD数据库

        public string IsBGD
        {
            get { return isBGD; }
            set { isBGD = value; }
        }
        string excelCellName;  //单元格名字(如果该单元格在记录行下面需要配置)

        public string ExcelCellName
        {
            get { return excelCellName; }
            set { excelCellName = value; }
        }

        //{{ 2013.7.16 by dugowe
        string isDownToRcd="0"; //位于记录行下

        public string IsDownToRcd
        {
            get { return isDownToRcd; }
            set { isDownToRcd = value; }
        }
        string isBool="0"; // 值是不是bool类型
        public string IsBool
        {
            get { return isBool; }
            set { isBool = value; }
        }

        // 表头, 分析元素上方
        private string isRightToAnalItem = "0";
        public string IsRightToAnalItem
        {
            get { return isRightToAnalItem; }
            set { isRightToAnalItem = value; }
        }
    }
    
    // 记录行
    [Serializable]
    public class RcdCellTemplate
    {
        string columnIndex;  //列(行)号从1开始

        public string ColumnIndex
        {
            get { return columnIndex; }
            set { columnIndex = value; }
        }
        string fieldName;  //对应的数据库字段名字

        public string FieldName
        {
            get { return fieldName; }
            set { fieldName = value; }
        }

        string allowNull = "1"; //是否允许空
        public string AllowNull
        {
            get { return allowNull; }
            set { allowNull = value; }
        }

        string bindTableName = ""; // 下拉列表绑定的表名
        public string BindTableName
        {
            get { return bindTableName; }
            set { bindTableName = value; }
        }

        string bindTableChineseName = "";  // 绑定表的中文名
        public string BindTableChineseName
        {
            get { return bindTableChineseName; }
            set { bindTableChineseName = value; }
        }

        string bindFieldName = "";// 绑定的字段名(外键)(字典表不用写)
        public string BindFieldName
        {
            get { return bindFieldName; }
            set { bindFieldName = value; }
        }
        private string bindFieldChineseName = ""; //绑定字段的中文名

        public string BindFieldChineseName
        {
            get { return bindFieldChineseName; }
            set { bindFieldChineseName = value; }
        }

        private string isBGD = "1"; // BGD数据库
        public string IsBGD
        {
            get { return isBGD; }
            set { isBGD = value; }
        }

        string isBool = "0"; //值是不是bool类型
        public string IsBool
        {
            get { return isBool; }
            set { isBool = value; }
        }


        private string isRightToAnalItem = "0";
        public string IsRightToAnalItem
        {
            get { return isRightToAnalItem; }
            set { isRightToAnalItem = value; }
        }
    }
    
    // Excel
    [Serializable]
    public class ExcelTemplate
    {

        string excelFileName;  // Excel文件名字

        public string ExcelFileName
        {
            get { return excelFileName; }
            set { excelFileName = value; }
        }
        string tableName;   // 数据库表名

        public string TableName
        {
            get { return tableName; }
            set { tableName = value; }
        }
        string chineseName;  // 表中文名字

        public string ChineseName
        {
            get { return chineseName; }
            set { chineseName = value; }
        }

        string recordStartRowIndex; // 记录行开始行号
        public string RecordStartRowIndex
        {
            get { return recordStartRowIndex; }
            set { recordStartRowIndex = value; }
        }

        string titleRowIndex; // 标题行Index
        public string TitleRowIndex
        {
            get { return titleRowIndex; }
            set { titleRowIndex = value; }
        }

        string titleColumnIndex; // 标题列Index
        public string TitleColumnIndex
        {
            get { return titleColumnIndex; }
            set { titleColumnIndex = value; }
        }

        string excelRcdRows;   // Excel默认记录行数
        public string ExcelRcdRows
        {
            get { return excelRcdRows; }
            set { excelRcdRows = value; }
        }

        string analItemStartColumn;  // 分析元素开始列
        public string AnalItemStartColumn
        {
            get { return analItemStartColumn; }
            set { analItemStartColumn = value; }
        }
        string isVertical = "0";  //数据位竖向的

        public string IsVertical
        {
            get { return isVertical; }
            set { isVertical = value; }
        }
        string tag;

        public string Tag
        {
            get { return tag; }
            set { tag = value; }
        }
        string tagRowIndex;

        public string TagRowIndex
        {
            get { return tagRowIndex; }
            set { tagRowIndex = value; }
        }
        string tagColIndex;

        public string TagColIndex
        {
            get { return tagColIndex; }
            set { tagColIndex = value; }
        }
        string sheetName;

        public string SheetName
        {
            get { return sheetName; }
            set { sheetName = value; }
        }
        ///////////////////////////////////////////////////////////////////////////////
        //////////////////////////////////////////////////////////////////////////

        public FixedCellTemplate[] FixedCells = new FixedCellTemplate[] { };
        public RcdCellTemplate[] RcdCells = new RcdCellTemplate[] { };

        public string[] TableKeys = new string[] { }; // 主键


        // 从配置文件中查找数据库字段对应的模板对象(FixedCellTemplate/RcdCellTemplate)
        public object FindCellByFieldName(string FieldName)
        {
            for (int i = 0; i < FixedCells.Length; i++)
            {
                if (FixedCells[i].FieldName == FieldName)
                {
                    return FixedCells[i];
                }
            }
            for (int i = 0; i < RcdCells.Length; i++)
            {
                if (RcdCells[i].FieldName == FieldName)
                {
                    return RcdCells[i];
                }
            }
            return null;
        }

        // 从配置文件中查找 行和列 对应的模板对象(FixedCellTemplate)
        public object FindFixedCellByIndex(string rowIndex,string ColIndex)
        {
            for (int i = 0; i < FixedCells.Length; i++)
            {
                if (FixedCells[i].RowIndex == rowIndex && FixedCells[i].ColumnIndex == ColIndex)
                {
                    return FixedCells[i];
                }
            }
           
            return null;
        }

        // 从配置文件中查找 列 对应的模板对象(RcdCellTemplate)
        public object FindRcdCellByIndex( string Index)
        {
            for (int i = 0; i < RcdCells.Length; i++)
            {
                if (RcdCells[i].ColumnIndex == Index)
                {
                    return RcdCells[i];
                }
            }
            return null;
        }
        
        //
        public void Save(string fileName)
        {
            File.Delete(fileName);
            FileStream fileStream = new FileStream(fileName, FileMode.OpenOrCreate);
            XmlSerializer xml = new XmlSerializer(typeof(ExcelTemplate));
            xml.Serialize(fileStream, this);
            fileStream.Close();
        }

        /// <summary>
        /// 根据文件名加载ExcelTemplate
        /// </summary>
        /// <param name="fileName">相对路径</param>
        /// <returns></returns>
        public static ExcelTemplate Load(string fileName)
        {
            FileStream fileStream = null;
            if (!File.Exists(fileName)) 
                return null;

            fileStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read);
            XmlSerializer xml = new XmlSerializer(typeof(ExcelTemplate));
            ExcelTemplate template = xml.Deserialize(fileStream) as ExcelTemplate;
            fileStream.Close();

            // 如果未配置
            //for (int i = 0; i < template.RcdCells.Length; i++)
            //{
            //    if (i == 0 && template.RcdCells[i].ColumnIndex.ToInt() == 0)
            //    {
            //        template.RcdCells[i].ColumnIndex = "1";
            //    }
            //    else if (template.RcdCells[i].ColumnIndex.ToInt() == 0)
            //    {
            //        template.RcdCells[i].ColumnIndex = (template.RcdCells[i - 1].ColumnIndex.ToInt() + 1).ToString();
            //    }
            //}

            return template;
        }
    }
    public class ExcelMgr
    {
        private double maxValue = 100000000;
        private double minValue = -100000000;
        public IDALFactory dbInstance;
        public ExcelMgr(IDALFactory dbInstance)
        {
            this.dbInstance = dbInstance;
        }
        public string FolderImport;     // 导入时选择的文件夹路径
        public string FolderExport;     // 导出时选择的文件夹路径
        public int AnalItemCount = 5;   // 分析元素列默认为5
        string ChkCell_Right = "√";  // 打勾的单元格

        List<string> fileNames = new List<string>();        // Excel名称
        List<string> configFileNames = new List<string>();  // 配置文件路径

        public int colSpan = 1;  //竖表列跨单元格

        // 进度条
        string progTitle;
        string ProgCurOperation;
        ProgressList ProgList = new ProgressList();
        double CurProg;

        public List<ExcelTemplate> templates = new List<ExcelTemplate>();   // 一个配置文件对应1个Template类

        public DataTable TemplateData;  //导出模板需要的数据(参考CommonExcelOp 57行)
        public DataTable TemplateAnalItems;  //分析元素对应的数据(参考CommonExcelOp 293行)

        
        bool IsValidateSuccess = true; //是否通过验证
        public ExportHandler GetExportDataTable;  // 导出获取数据的方法
        public ConvertNameToCodeHandler ConvertNameToCode; //转换成code的方法

        Dictionary<ExcelTemplate, List<string>> templatesColumns = new Dictionary<ExcelTemplate, List<string>>();  // 保存模板对应的列名
        Dictionary<ExcelTemplate, List<string>> templatesColumnsValue = new Dictionary<ExcelTemplate, List<string>>();//保存模板对应的列的值
        Dictionary<ExcelTemplate, DataTable> templateAnalElem = new Dictionary<ExcelTemplate, DataTable>(); //保存分析元素列
        Dictionary<ExcelTemplate, string> analTableName = new Dictionary<ExcelTemplate, string>(); //保存分析元素对应的表名
        Dictionary<object, FieldValidateHandler> FieldsValidate = new Dictionary<object, FieldValidateHandler>();  //保存某个单元格对应的验证方法(参考CommonExcelOp类中添加操作223行)
        Dictionary<object, string> FieldsPrefix = new Dictionary<object, string>();  //保存某个单元格对应的前缀(参考CommonExcelOp类中添加操作)
       
        Dictionary<ExcelTemplate, OperationAfterSave> OperationAfterSave = new Dictionary<ExcelTemplate, OperationAfterSave>(); //保存完毕后对应的操作(参考CommonExcelOp类中添加操作)
        Dictionary<ExcelTemplate, OperationAfterExport> OperationAfterExport = new Dictionary<ExcelTemplate, OperationAfterExport>(); //导出完毕后对应的操作(参考CommonExcelOp类中添加操作)
        Dictionary<ExcelTemplate, OperationBeforeSave> OperationBeforeSave = new Dictionary<ExcelTemplate, OperationBeforeSave>(); //保存前对应的操作(参考CommonExcelOp类中添加操作)
        Dictionary<ExcelTemplate, OperationBeforeValidate> OperationBeforeValidate = new Dictionary<ExcelTemplate, OperationBeforeValidate>(); //验证前对应操作(参考CommonExcelOp类中添加操作)
        Dictionary<ExcelTemplate, Dictionary<string, ConvertHandler>> ConvertBeforeSave = new Dictionary<ExcelTemplate, Dictionary<string, ConvertHandler>>();  //某个单元格保存前需要转换。例如绝对路径转换为相对路径(参考CommonExcelOp类中添加操作)
        Dictionary<ExcelTemplate, List<CommonValidate>> commonValidates = new Dictionary<ExcelTemplate, List<CommonValidate>>();
        Dictionary<ExcelTemplate,Func<DataTable,string>> WholeValidates=new Dictionary<ExcelTemplate,Func<DataTable,string>>();

        List<DataRow> nullRows = new List<DataRow>();
        Dictionary<ExcelTemplate, Action<DataTable>> OperationAfterSaveAll = new Dictionary<ExcelTemplate, Action<DataTable>>();

        Dictionary<DataRow, string[]> rowReferToData = new Dictionary<DataRow, string[]>(); //保存读取到的DataRow对应的sheet页以及行信息
        public bool GroupByFiexedCells = true;

        
        bool isAutoMerge = false;  //是否自动合并单元格
        string mergeTagCol = "";  //自动合并用到的标识列
        /// <summary>
        /// 设置配置文件
        /// </summary>
        /// <param name="fileNames">配置文件相对路径(可以多个)</param>
        public void SetConfigFileName( params string[] fileNames)
        {
            for (int i = 0;i < fileNames.Length;i++)// string fileName in fileNames)
            {
                string fileName = fileNames[i];
                configFileNames.Add(fileName);

                // 文件是否存在
                if (!File.Exists(System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, fileNames[i])))
                {
                    throw new Exception("Excel配置文件" + fileNames[i] + "不存在!");
                }
                else
                {
                    fileName = System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, fileNames[i]);
                }

                ExcelTemplate template = ExcelTemplate.Load(fileName);  //将配置文件读取到模板类中
                templates.Add(template);
            }
        }
        
        /// <summary>
        /// 设置分析元素列的列
        /// </summary>
        /// <param name="configFileName"></param>
        /// <param name="dt">包含两列(名称、单位)</param>
        public void SetAnalElements(string configFileName,string analItemTableName)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("PrjID");
            dt.Columns.Add("SampNo");
            dt.Columns.Add("ItemNo");
            dt.Columns.Add("ElemValue");
            dt.Columns.Add("SurvNo");
            dt.Columns.Add("ElemUnit");
            dt.Columns.Add("ElemName");
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    templateAnalElem.Add(templates[i], dt);
                    analTableName.Add(templates[i], analItemTableName);
                    break;
                }
            }
        }
    
        /// <summary>
        /// 指定模板添加一个字段
        /// </summary>
        /// <param name="configFileName">配置文件名</param>
        /// <param name="Column">列</param>
        /// <param name="value">值</param>
        public void AddColumn(string configFileName,string Column,string value)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] != configFileName) continue;
                if (!templatesColumns.Keys.Contains(templates[i]))
                {
                    templatesColumns.Add(templates[i], new List<string>());
                    templatesColumnsValue.Add(templates[i], new List<string>());

                }
                templatesColumns[templates[i]].Add(Column);
                templatesColumnsValue[templates[i]].Add(value);
            }
        }
        /// <summary>
        /// 所有模板都加一个字段
        /// </summary>
        /// <param name="Column">列</param>
        /// <param name="value">值</param>
        public void AddColumn(string Column, string value)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (!templatesColumns.Keys.Contains(templates[i]))
                {
                    templatesColumns.Add(templates[i], new List<string>());
                    templatesColumnsValue.Add(templates[i], new List<string>());
                }
                templatesColumns[templates[i]].Add(Column);
                templatesColumnsValue[templates[i]].Add(value);
            }
        }
        /// <summary>
        /// 设置为智能合并单元格
        /// </summary>
        /// <param name="tagColField">合并单元格用于判断相等就合并的列</param>
        public void SetAutoMergeCols(string tagColField)
        {
            this.isAutoMerge = true;
            this.mergeTagCol = tagColField;
        }
        /// <summary>
        /// 设置单元格验证
        /// </summary>
        /// <param name="configFileName">模板类</param>
        /// <param name="FieldName">单元格对应的字段名</param>
        /// <param name="validate">验证方法</param>
        public void SetFieldValidate(string configFileName, string FieldName,FieldValidateHandler validate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    object obj = templates[i].FindCellByFieldName(FieldName);
                    if (obj != null)
                        FieldsValidate.Add(obj, validate);
                }
            }
        }
        public void AddValidate(string configFileName, CommonValidate validate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    if (commonValidates.ContainsKey(templates[i]))
                    {
                        commonValidates[templates[i]].Add(validate);
                    }
                    else
                    {
                        List<CommonValidate> validates = new List<CommonValidate>();
                        validates.Add(validate);
                        commonValidates.Add(templates[i], validates);
                    }
                }
            }
        }
        /// <summary>
        /// 添加整体验证
        /// </summary>
        /// <param name="configFileName"></param>
        /// <param name="validate"></param>
        public void AddValidate(string configFileName, Func<DataTable,string> validate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    if (commonValidates.ContainsKey(templates[i]))
                    {
                        WholeValidates[templates[i]]=validate;
                    }
                    else
                    {
                        WholeValidates.Add(templates[i], validate);
                    }
                }
            }
        }
        /// <summary>
        /// 设置单元格前缀
        /// </summary>
        /// <param name="configFileName">模板类</param>
        /// <param name="FieldName">单元格对应的字段名</param>
        /// <param name="validate">前缀</param>
        public void SetFieldPrefix(string configFileName,string FieldName,string Prefix)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    object obj = templates[i].FindCellByFieldName(FieldName);
                    if (obj != null)
                        FieldsPrefix.Add(obj, Prefix);
                }
            }
        }
        /// <summary>
        /// 设置保存前的操作
        /// </summary>
        /// <param name="configFileName">模板</param>
        /// <param name="Opreate">操作</param>
        public void SetOperationAfterSave(string configFileName,OperationAfterSave Opreate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    OperationAfterSave.Add(templates[i],Opreate);
                }
            }
        }
        public void SetOperationAfterSaveAll(string configFileName, Action<DataTable> Opreate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    OperationAfterSaveAll.Add(templates[i], Opreate);
                }
            }
        }
        /// <summary>
        /// 设置导出后的操作
        /// </summary>
        /// <param name="configFileName">模板</param>
        /// <param name="Opreate">操作</param>
        public void SetOperationAfterExport(string configFileName, OperationAfterExport Opreate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    OperationAfterExport.Add(templates[i], Opreate);
                }
            }
        }
        /// <summary>
        /// 设置保存前的操作
        /// </summary>
        /// <param name="configFileName"></param>
        /// <param name="Opreate"></param>
        public void SetOperationBeforeSave(string configFileName, OperationBeforeSave Opreate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    OperationBeforeSave.Add(templates[i], Opreate);
                }
            }
        }
        /// <summary>
        /// 设置验证前的操作
        /// </summary>
        /// <param name="configFileName"></param>
        /// <param name="Opreate"></param>
        public void SetOperationBeforeValidate(string configFileName, OperationBeforeValidate Opreate)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    OperationBeforeValidate.Add(templates[i], Opreate);
                }
            }
        }
        /// <summary>
        /// 转换列(例如绝对路径转换为相对路径)
        /// </summary>
        /// <param name="configFileName"></param>
        /// <param name="FieldName"></param>
        /// <param name="validate"></param>
        public void SetFieldConvert(string configFileName, string ConvertFieldName,ConvertHandler converter)
        {
            for (int i = 0; i < configFileNames.Count; i++)
            {
                if (configFileNames[i] == configFileName)
                {
                    object obj = templates[i].FindCellByFieldName(ConvertFieldName);
                    if (obj != null)
                    {
                        if (ConvertBeforeSave.ContainsKey(templates[i]))
                        {
                            ConvertBeforeSave[templates[i]].Add(ConvertFieldName, converter);
                        }
                        else
                        {
                            Dictionary<string, ConvertHandler> Converter = new Dictionary<string, ConvertHandler>();
                            Converter.Add(ConvertFieldName, converter);
                            ConvertBeforeSave.Add(templates[i], Converter);
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 根据配置文件生成一个DataTable,(该DataTable只有列名)
        /// </summary>
        /// <returns></returns>
        
        private List<DataTable> CreateDataTable()
        {
            List<DataTable> tables = new List<DataTable>();
            for (int index=0;index<templates.Count;index++)
            {
                DataTable dt = new DataTable();
                ExcelTemplate template = templates[index];
                DataColumn dc = null;

                /*-----------根据一个模板类生成一个DT---------------*/
                if (template.FixedCells != null)  //固定单元格
                {
                    for (int i = 0; i < template.FixedCells.Length; i++)
                    {
                        if (dt.Columns.Contains(template.FixedCells[i].FieldName)) continue;
                        dc = new DataColumn();
                        dc.ColumnName = template.FixedCells[i].FieldName;
                        dt.Columns.Add(dc);
                    }
                }
                if (template.RcdCells != null)  //记录行单元格
                {
                    for (int i = 0; i < template.RcdCells.Length; i++)
                    {
                        if (dt.Columns.Contains(template.RcdCells[i].FieldName)) continue;
                        dc = new DataColumn();
                        dc.ColumnName = template.RcdCells[i].FieldName;
                        dt.Columns.Add(dc);
                    }
                }
                if (templatesColumns.Keys.Contains(template)) //通过AddColumn方法加进来的列
                {
                    foreach (string column in templatesColumns[template])
                    {
                        if (dt.Columns.Contains(column)) continue;
                        dc = new DataColumn();
                        dc.ColumnName = column;
                        dt.Columns.Add(dc);
                    }
                }//生成完毕
                tables.Add(dt);
            }
            return tables;
        }

        private string GetProgTitle()
        {
            return this.progTitle;
        }
        private string GetProgCurOperation()
        {
            return this.ProgCurOperation;
        }
        private double GetProgPosition()
        {
            return this.CurProg;
        }
        WinProgress win;
        /// <summary>
        /// 导入单个文件
        /// </summary>
        /// <returns></returns>
        public virtual  bool ImportSingleData()
        {
            try
            {
                System.Windows.Forms.OpenFileDialog fileDialog = new System.Windows.Forms.OpenFileDialog();
                fileDialog.Filter = "Excel文件(*.xls,*.xlsx)|*.xls;*.xlsx";
                if (fileDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    FolderImport = Path.GetDirectoryName(fileDialog.FileName);
                    fileNames = new List<string>();
                    fileNames.Add(fileDialog.FileName);
                     win = new WinProgress(
                              new ReturnStrHandler(GetProgTitle),
                              new ReturnStrHandler(GetProgCurOperation),
                              new ReturnDoubleHandler(GetProgPosition),
                              this.ProgList,
                              new NoReturnHandler(DoImport)
                               );
                    win.ShowDialog();
                }
                return true;
            }
            catch (Exception ex)
            {
                ShowMessage.Notice("操作异常:"+ ex.Message);
                return false;
            }
         
        }
        /// <summary>
        /// 导入整个流程
        /// </summary>
        /// <returns></returns>
        public bool ImportMultiData()
        {
            try
            {
                System.Windows.Forms.FolderBrowserDialog folderDialog = new System.Windows.Forms.FolderBrowserDialog();
                if (folderDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    FolderImport = folderDialog.SelectedPath;
                    string[] files = folderDialog.SelectedPath.GetFileNames("xlsx", "xls");
                    string[] filesPath = folderDialog.SelectedPath.GetFiles("xlsx", "xls");
                    fileNames = new List<string>();
                    for (int i = 0; i < templates.Count; i++)   //排序
                    {
                        for (int j = 0; j < files.Length; j++)
                        {
                            if (templates[i].ExcelFileName.Contains(files[j]))
                            {
                                if (!fileNames.Contains(filesPath[j]))
                                {
                                    fileNames.Add(filesPath[j]);
                                    break;
                                }
                            }
                        }
                    }
                    WinProgress win = new WinProgress(
                              new ReturnStrHandler(GetProgTitle),
                              new ReturnStrHandler(GetProgCurOperation),
                              new ReturnDoubleHandler(GetProgPosition),
                              this.ProgList,
                              new NoReturnHandler(DoImport)
                               );
                    win.ShowDialog();
                }
                return true;
            }
            catch (Exception ex)
            {
                ShowMessage.Notice("操作异常"+ex.Message);
                return false;
            }
           
        }
        /// <summary>
        /// 导入方法
        /// </summary>
        private void DoImport()
        {
            CurProg = 0;

            nullRows = new List<DataRow>();
            rowReferToData = new Dictionary<DataRow, string[]>();

            ProgCurOperation = "正在初始化";
            Dictionary<DataRow, DataTable> analItem = new Dictionary<DataRow, DataTable>();

            for (int index = 0; index < fileNames.Count; index++) //循环导入所有配置文件
            {
                FileInfo file = new FileInfo(fileNames[index]);  
                List<DataTable> tables = CreateDataTable(); //生成保存数据用的DataTable

                progTitle = "导入数据";
                for (int i = 0; i < templates.Count; i++)
                {
                    if (fileNames.Count<=1 || templates[i].ExcelFileName.Contains(Path.GetFileName(fileNames[index])))
                    {
                        ProgCurOperation = "正在读取" + templates[i].ChineseName + "数据";
                        ReadData(templates[i], tables[i], fileNames[index], analItem); //读取数据
                        CurProg = CurProg + (30 / fileNames.Count) / fileNames.Count / templates.Count;  //设置进度
                    }
                }
                ProgCurOperation = "正在转换数据";

                /*-----------读取到的DataTable中的外键字段(读取到的为Name)转换为Code------------*/
                List<string> tableNames = new List<string>();  
                List<string> FieldNames = new List<string>();
                for (int i = 0; i < tables.Count; i++)
                {
                    for (int j = 0; j < templates[i].FixedCells.Length; j++)
                    {
                        if (!string.IsNullOrEmpty(templates[i].FixedCells[j].BindTableName))
                        {
                            tableNames.Add(templates[i].FixedCells[j].BindTableName.ToLower());
                            FieldNames.Add(templates[i].FixedCells[j].FieldName);
                        }
                    }
                    for (int j = 0; j < templates[i].RcdCells.Length; j++)
                    {
                        if (!string.IsNullOrEmpty(templates[i].RcdCells[j].BindTableName))
                        {
                            tableNames.Add(templates[i].RcdCells[j].BindTableName.ToLower());
                            FieldNames.Add(templates[i].RcdCells[j].FieldName);
                        }
                    }
                    if (ConvertNameToCode != null)
                    {
                        ConvertNameToCode(tables[i], tableNames, FieldNames); //将Name转换为Code
                    }   
                } //转换完毕
                CurProg = CurProg + 10 / fileNames.Count;

                // 验证数据
             
                for (int i = 0; i < tables.Count; i++)
                {
                  
                    /*---------------一行一行验证数据------------*/
                    if (tables[i].Rows.Count > 0)
                    {
                        if (fileNames.Count <= 1 || templates[i].ExcelFileName.Contains(Path.GetFileName(fileNames[index])))
                        {
                            if (OperationBeforeValidate.ContainsKey(templates[i]))
                            {
                                OperationBeforeValidate[templates[i]](tables[i]);
                            }
                            ProgCurOperation = "正在验证" + templates[i].ChineseName + "数据完整性";
                             //整体验证
                            if(WholeValidates.ContainsKey(templates[i]))
                            {
                                string info=WholeValidates[templates[i]](tables[i]);
                                if(!string.IsNullOrEmpty(info))
                                {
                                    IsValidateSuccess = false;
                                    ProgressListItem item = new ProgressListItem();
                                    item.ImageType = ProgImageTypeEm.Error;
                                    item.Text =info;
                                    this.ProgList.Append(item);
                                }
                            }
                            ValidateData(templates[i], tables[i],analItem);
                        }
                        CurProg = CurProg + 10 / fileNames.Count / fileNames.Count / tables.Count;
                    }
                    //验证完毕
                }
                if (!IsValidateSuccess)  //验证没通过就返回
                {
                    ProgCurOperation = "";
                    CurProg = 100;
                    return;
                }
                int AddCount = 0;
                int UpdateCount = 0;

                // 保存数据
                for (int i = 0; i < templates.Count; i++)
                {
                    AddCount = 0;
                    UpdateCount = 0;
                    if (fileNames.Count <= 1 || templates[i].ExcelFileName.Contains(Path.GetFileName(fileNames[index])))
                    {
                        ProgCurOperation = "正在写入" + templates[i].ChineseName + "数据";
                        for (int j = 0; j < tables[i].Rows.Count; j++)
                        {
                            if (nullRows.Contains(tables[i].Rows[j])) continue;
                            /*------------写入数据--------------*/
                            string[] columnValues = new string[templates[i].TableKeys.Length];
                            for (int k = 0; k < templates[i].TableKeys.Length; k++)
                            {
                                if (tables[i].Columns.Contains(templates[i].TableKeys[k]))
                                    columnValues[k] = tables[i].Rows[j][templates[i].TableKeys[k]].ToString();
                                else
                                    columnValues[k] = null;
                            }
                            DataRow drOri = tables[i].Rows[j];//.Copy();
                            /*--------------转换列-------------*/
                            if (ConvertBeforeSave.ContainsKey(templates[i]))
                            {
                                foreach (string obj in ConvertBeforeSave[templates[i]].Keys)
                                {
                                    tables[i].Rows[j][obj] = ConvertBeforeSave[templates[i]][obj](tables[i].Rows[j][obj.ToString()].ToString(), tables[i].Rows[j]);
                                }
                            }//转换完成
                            if (dbInstance.IsExist(templates[i].TableName, templates[i].TableKeys, columnValues) && templates[i].TableKeys!=null && templates[i].TableKeys.Length>0)
                            {
                                UpdateCount++;
                                if (OperationBeforeSave.ContainsKey(templates[i]))
                                {
                                    OperationBeforeSave[templates[i]](drOri, EditState.Edit);
                                }
                                win.Dispatcher.Invoke(new Action(() =>
                                {
                                    dbInstance.Update(templates[i].TableName, tables[i].Rows[j], templates[i].TableKeys, columnValues);
                                }));
                                if (OperationAfterSave.ContainsKey(templates[i]))
                                {
                                    OperationAfterSave[templates[i]](drOri, EditState.Edit);
                                }
                            }
                            else
                            {
                                AddCount++;
                                if (OperationBeforeSave.ContainsKey(templates[i]))
                                {
                                    OperationBeforeSave[templates[i]](drOri, EditState.New);
                                }
                                win.Dispatcher.Invoke(new Action(() =>
                                {
                                    dbInstance.Add(templates[i].TableName, tables[i].Rows[j]);
                                }));
                                if (OperationAfterSave.ContainsKey(templates[i]))
                                {
                                    OperationAfterSave[templates[i]](drOri, EditState.New);
                                }
                            }
                        }
                        /*------保存分析元素------*/
                        if (templateAnalElem.ContainsKey(templates[i]))
                        {
                            DataTable dt = templateAnalElem[templates[i]];
                            if (dt.Rows.Count > 0)
                            {
                                for (int j = 0; j < dt.Rows.Count; j++)
                                {
                                    if (!string.IsNullOrEmpty(dt.Rows[j]["SampNo"].ToString()))
                                        dbInstance.Delete(analTableName[templates[i]], new string[] { "SampNo" }, new string[] { dt.Rows[j]["SampNo"].ToString() });
                                }
                                dbInstance.Add(analTableName[templates[i]], dt);
                            }
                        }//保存分析元素
                        CurProg = CurProg + 50 / fileNames.Count / fileNames.Count / templates.Count ;
                        //写入完毕
                        if (OperationAfterSaveAll.ContainsKey(templates[i]))
                        {
                            OperationAfterSaveAll[templates[i]](tables[i]);
                        }
                        ProgressListItem item = new ProgressListItem();
                        item.ImageType = ProgImageTypeEm.None;
                        string xlsName = "";
                        if (!string.IsNullOrEmpty(templates[i].SheetName))
                            xlsName = templates[i].SheetName;
                        else
                            xlsName = templates[i].ChineseName;
                            item.Text = "'" + xlsName + "'添加数据:" + AddCount.ToString() + "条,更新数据:" + UpdateCount.ToString() + "条";
                        this.ProgList.Append(item);
                        
                    }
                }
                CurProg = CurProg + 100 / fileNames.Count;
            }
            ProgCurOperation = "";
            CurProg = 100;
            return;
        }


        private void ReadData(ExcelTemplate template, DataTable table, string filePath, Dictionary<DataRow, DataTable> analItem)
        {

            BaseExcel excelOp = new BaseExcel();
            if (!excelOp.Open(filePath)) {
                ShowMessage.Notice("Excel打开失败!");
                return;
            }

            DataRow dr = null;

            if (template.TitleRowIndex.ToInt() > 0 && template.TitleColumnIndex.ToInt() > 0)
            {
                table.TableName= excelOp.ReadData(template.TitleRowIndex.ToInt(), template.TitleColumnIndex.ToInt());  //读取表头
            }
            string fileName = Path.GetFileName(template.ExcelFileName);
            /*--------------------验证表头--------------------*/
            string chineseName = "";
            if (!string.IsNullOrEmpty(template.ChineseName))
            {
                chineseName = template.ChineseName.TrimNew();
                if (template.ChineseName.TrimNew() != null && template.ChineseName.TrimNew().Contains("-"))
                {
                    chineseName = template.ChineseName.TrimNew().Substring(0, template.ChineseName.TrimNew().IndexOf('-'));
                }
                if (!table.TableName.Contains(chineseName))
                {
                    IsValidateSuccess = false;
                    ProgressListItem item = new ProgressListItem();
                    item.ImageType = ProgImageTypeEm.Error;
                    item.Text = "表格格式有误!请检查是否选错表格";
                    this.ProgList.Append(item);
                    excelOp.Close();
                    return;
                }
            }
            //int irowIndex = 0;
            //if (template.ExcelRcdRows.ToInt() <= 0)
            //    irowIndex = -1;
            List<string> allSheets = excelOp.GetAllSheet();
            for (int iSheet = 0; iSheet < allSheets.Count; iSheet++)
            {
                excelOp.ActivateSheet(allSheets[iSheet]);
                int nullCount = 0;// 空行数
                int Row = template.RecordStartRowIndex.ToInt();
                if (template.TitleRowIndex.ToInt() > 0 && template.TitleColumnIndex.ToInt() > 0)
                {
                    table.TableName = excelOp.ReadData(template.TitleRowIndex.ToInt(), template.TitleColumnIndex.ToInt());  //读取表头
                }
                if (!table.TableName.Contains(chineseName))
                {
                    continue;
                }
                int addRows = 0;  //插入或者删除的行数。
                if (!string.IsNullOrEmpty(template.Tag) && !string.IsNullOrEmpty(template.TagColIndex) && !string.IsNullOrEmpty(template.TagRowIndex))
                {
                    for (int iRowIndex = template.RecordStartRowIndex.ToInt(); ; iRowIndex++)
                    {
                        string txt = excelOp.ReadData(iRowIndex, template.TagColIndex.ToInt());
                        if (txt.TrimNew().StartsWith(template.Tag.TrimNew()))
                        {
                            addRows =iRowIndex- template.TagRowIndex.ToInt();
                            if (template.FixedCells != null)
                            {
                                foreach (FixedCellTemplate cell in template.FixedCells)
                                {
                                    if (cell.IsDownToRcd.ToInt() > 0)
                                    {
                                        cell.RowIndex = (cell.RowIndex.ToInt()+ iRowIndex - template.TagRowIndex.ToInt()).ToString();
                                    }
                                }
                            }
                            break;
                        }
                        else if (iRowIndex > 10000)  //大于10000行就认为破坏了Excel结构，免得死循环
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            item.Text = "表格格式有误!请使用系统导出的模板，勿修改模板格式";
                            this.ProgList.Append(item);
                            return;
                        }
                    }
                }
                //读取数据(每次循环读取一行)
                while(true)
                {
                    dr = table.NewRow();
                    if (template.RcdCells != null && template.RcdCells.Length > 0)  //读取记录行
                    {
                        //for (int i = 0; i < template.RcdCells.Length; i++)
                        //{
                       
                        foreach (RcdCellTemplate cell in template.RcdCells) 
                        {
                            int addCols = 0;
                            if (cell.IsRightToAnalItem.ToInt() > 0) //如果在分析元素列右边的就计算出插入的列
                            {
                                int colIndex = cell.ColumnIndex.ToInt();
                                while (true)
                                {
                                    if (excelOp.ReadData(template.RecordStartRowIndex.ToInt() - 2, colIndex).Trim() == cell.BindFieldChineseName)
                                        break;
                                    addCols++;
                                    colIndex++;
                                }
                            }
                            int ReadIndex = cell.ColumnIndex.ToInt();
                            if (cell.IsRightToAnalItem.ToInt() > 0)
                            {
                                ReadIndex += addCols;
                            }
                            if (cell.IsBool.ToInt() > 0) //如果是布尔类型，就保存true或者false，不为空就是True
                            {
                                if ((template.IsVertical.ToInt()<=0 && !string.IsNullOrEmpty(excelOp.ReadData(Row, ReadIndex)))||(template.IsVertical.ToInt()>0 && !string.IsNullOrEmpty(excelOp.ReadData(ReadIndex, Row))))
                                {
                                    dr[cell.FieldName] = true;
                                }
                                else
                                {
                                    dr[cell.FieldName] = false;
                                }
                            }
                            else
                            {
                                if(template.IsVertical.ToInt()<=0)
                                    dr[cell.FieldName] = excelOp.ReadData(Row, ReadIndex);
                                else 
                                    dr[cell.FieldName] = excelOp.ReadData(ReadIndex,Row );
                            }
                        }
                       // }
                        if (dr == null) break;
                        bool IsEnd = false;
                        bool isNullRow = false;
                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            if (!string.IsNullOrEmpty(dr[i].ToString().Trim()) && dr[i].ToString().ToLower() != "false") break;
                            if ((string.IsNullOrEmpty(dr[i].ToString().Trim()) || dr[i].ToString().ToLower() == "false") && i == table.Columns.Count - 1)    //如果碰到完全空的一行就记录。
                            {
                                nullCount++;
                                nullRows.Add(dr);
                                isNullRow = true;
                                if (nullCount >= 5)
                                {
                                    IsEnd = true;
                                }
                            }
                        }
                        if (!isNullRow)
                        {
                            nullCount = 0;
                        }
                        if (IsEnd) break;
                    }
                    else
                    {
                        dr = table.NewRow();
                    }
                    if (template.FixedCells != null) //读取固定行数据
                    {
                        foreach (FixedCellTemplate cell in template.FixedCells)
                        {
                            if (template.RcdCells != null && template.RcdCells.Length > 0)
                            {
                                int addCols = 0;
                              
                                if (cell.IsRightToAnalItem.ToInt() > 0)
                                {
                                    if (templateAnalElem.ContainsKey(template))
                                    {
                                        if (templateAnalElem[template].Rows.Count > AnalItemCount)
                                        {
                                            addCols = templateAnalElem[template].Rows.Count - AnalItemCount;
                                        }
                                    }
                                }
                                if (cell.IsBool.ToInt() > 0) //如果是布尔类型，就保存true或者false，不为空就是True
                                {
                                    if (!string.IsNullOrEmpty(excelOp.ReadData(cell.RowIndex.ToInt() , cell.ColumnIndex.ToInt() + addCols).Trim()))
                                    {
                                        dr[cell.FieldName] = true;
                                    }
                                    else
                                    {
                                        dr[cell.FieldName] = false;
                                    }
                                }
                                else 
                                {
                                    dr[cell.FieldName] = excelOp.ReadData(cell.RowIndex.ToInt() , cell.ColumnIndex.ToInt() + addCols);// = dr[cell.FieldName].ToStringNew() + excelOp.ReadData(cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt() + addCols);
                                }   
                            }
                            else 
                            {
                                if (cell.IsBool.ToInt() > 0) //如果是布尔类型，就保存true或者false，不为空就是True
                                {
                                    if (!string.IsNullOrEmpty(excelOp.ReadData(cell.RowIndex.ToInt() , cell.ColumnIndex.ToInt()).Trim()))
                                    {
                                        dr[cell.FieldName] = true;
                                    }
                                    else
                                    {
                                        dr[cell.FieldName] = false;
                                    }
                                }
                                else
                                {
                                    dr[cell.FieldName] = excelOp.ReadData(cell.RowIndex.ToInt() , cell.ColumnIndex.ToInt());// dr[cell.FieldName].ToStringNew() + excelOp.ReadData(cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt());
                                }
                            }
                        }
                    }
                    if (templatesColumns.Keys.Contains(template)) //给用AddColumn方法增加的列赋值
                    {
                        for (int i = 0; i < templatesColumns[template].Count; i++)
                        {
                            if (string.IsNullOrEmpty(dr[templatesColumns[template][i]].ToString()))
                                dr[templatesColumns[template][i]]=templatesColumnsValue[template][i];
                        }
                    }
                    //if (template.ChineseName.Contains("大体重"))
                    //{
                    //    if (string.IsNullOrEmpty(dr["SampNo"].ToString())&&dr["SurvNo"].ToString()!="1")
                    //    {
                    //        dr["SampNo"] = table.Rows[table.Rows.Count-1]["SampNo"];
                    //        dr["SamplingPos"] = table.Rows[table.Rows.Count-1]["SamplingPos"];
                    //        dr["RockName"] = table.Rows[table.Rows.Count-1]["RockName"];
                    //        dr["SamplingDate"] =  table.Rows[table.Rows.Count-1]["SamplingDate"];
                    //    }
                    //}
                    //读取分析元素
                    if (templateAnalElem.ContainsKey(template))
                    {
                        DataTable dtItem = templateAnalElem[template];
                        int column = template.AnalItemStartColumn.ToInt();
                        string tagString = excelOp.ReadData(template.RecordStartRowIndex.ToInt() - 2, column);
                        while (true)
                        {
                            if (string.IsNullOrEmpty(excelOp.ReadData(template.RecordStartRowIndex.ToInt()-1, column)) ||
                                tagString != excelOp.ReadData(template.RecordStartRowIndex.ToInt() - 2, column)
                                ) break;
                            string value= excelOp.ReadData(Row, column).Trim();
                            if (!string.IsNullOrEmpty(value))
                            {
                                if (dtItem.Rows.Count > 0)
                                {
                                    DataRow[] drs = dtItem.Select(string.Format(" SampNo='{0}' and ElemName='{1}'  ", dr["SampNo"].ToString(), excelOp.ReadData(template.RecordStartRowIndex.ToInt() - 1, column)));
                                    if (drs!=null && drs.Length>0)
                                    {
                                        column++;
                                        continue;
                                        
                                    }
                                }
                                DataRow drItem = dtItem.NewRow();
                                if (table.Columns.Contains("PrjID"))
                                {
                                    drItem["PrjID"] = dr["PrjID"];
                                }
                                if (table.Columns.Contains("SampNo"))
                                {
                                    drItem["SampNo"] = dr["SampNo"];
                                }
                                if (table.Columns.Contains("SurvNo"))
                                {
                                    drItem["SurvNo"] = dr["SurvNo"];
                                }
                                drItem["ItemNo"] = Guid.NewGuid();
                                drItem["ElemValue"] = value;
                                drItem["ElemName"] = excelOp.ReadData(template.RecordStartRowIndex.ToInt() - 1, column);
                            
                                dtItem.Rows.Add(drItem);
                            }
                            column++;
                        }
                        analItem.Add(dr, dtItem);
                    }
                    table.Rows.Add(dr);
                    string[] data=new string[2];
                    data[0] = allSheets[iSheet];
                    data[1] = Row.ToString();
                    rowReferToData.Add(dr, data);
                    Row=Row+colSpan;
                    if (template.RcdCells == null || template.RcdCells.Length == 0) break;
                    if(template.RcdCells!=null&&template.RcdCells.Length>0 &&!string.IsNullOrEmpty( template.TagRowIndex)&& Row>=template.TagRowIndex.ToInt()+addRows) break;

                }
            }

            excelOp.Close();
            //数据读取完毕
        }
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="IsTemplate"> 是否为模板</param>
        /// <param name="IsSelectedFolder">整体导出时为true</param>
        /// <returns></returns>
        public void ExportData(bool IsTemplate=false,bool IsSelectedFolder=false)
        {
            try
            {
                //if (GetExportDataTable == null && IsTemplate == false)
                //    return;

                System.Windows.Forms.FolderBrowserDialog folderDialog = new System.Windows.Forms.FolderBrowserDialog();
                // 设置对话框的说明信息
                folderDialog.Description = "请选择数据导出文件保存目录";

                //  if (IsSelectedFolder == true)//用户选择保存路径，导出文件 LCA 修改 2013-10-14 
                //{
                if (folderDialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    FolderExport = folderDialog.SelectedPath;
                }
                else
                {
                    return;
                }
                //}

                // 导出的excel名称
                List<string> ExcelNames = new List<string>();
                for (int i = 0; i < templates.Count; i++)
                {
                    if (!ExcelNames.Contains(templates[i].ExcelFileName))
                    {
                        ExcelNames.Add(templates[i].ExcelFileName);
                    }
                }
                if (ExcelNames.Count == 1)  //导出单个文件
                {
                    string fileName = Path.GetFileName(templates[0].ExcelFileName);
                    if (IsTemplate) fileName = Path.GetFileNameWithoutExtension(fileName) + " 模板" + Path.GetExtension(fileName);
                    if (!Directory.Exists(FolderExport))
                    {
                        if (Directory.Exists(System.IO.Path.Combine(Application.StartupPath, "Cache")) && File.Exists(System.IO.Path.Combine(Application.StartupPath, "Cache", fileName)))
                        {
                            if (FileHelper.IsFileOpened(System.IO.Path.Combine(Application.StartupPath, "Cache", fileName)))
                            {
                                ShowMessage.Error("请先关闭Excel文件");
                                return;
                            }
                            else
                            {
                                if (File.Exists(Path.Combine(FolderExport, fileName)) && ShowMessage.Question_YesNo("存在同名文件,继续导出将会删除同名文件，是否继续？") != System.Windows.MessageBoxResult.Yes)
                                {
                                    return;
                                }
                                System.IO.File.Delete(System.IO.Path.Combine(Application.StartupPath, "Cache", fileName));
                            }
                        }
                        if (!Directory.Exists(System.IO.Path.Combine(Application.StartupPath, "Cache")))
                            Directory.CreateDirectory(System.IO.Path.Combine(Application.StartupPath, "Cache"));
                        File.Copy(Path.Combine(Application.StartupPath, templates[0].ExcelFileName), Path.Combine(Application.StartupPath, "Cache", fileName));
                        CrWaitControl waitControl = new CrWaitControl(new Action(() =>
                        {
                            ExportSingle(Path.Combine(Application.StartupPath, "Cache", fileName), IsTemplate);
                        }));
                        return;
                    }
                    else
                    {
                        if (File.Exists(Path.Combine(FolderExport, fileName)) && FileHelper.IsFileOpened(Path.Combine(FolderExport, fileName)))
                        {
                            ShowMessage.Error("请先关闭Excel文件");
                            return;
                        }
                        else
                        {
                            if (File.Exists(Path.Combine(FolderExport, fileName)) &&ShowMessage.Question_YesNo("存在同名文件,继续导出将会删除同名文件，是否继续？") != System.Windows.MessageBoxResult.Yes)
                            {
                                return;
                            }
                            System.IO.File.Delete(Path.Combine(FolderExport, fileName));
                        }
                        File.Copy(Path.Combine(Application.StartupPath, templates[0].ExcelFileName), Path.Combine(FolderExport, fileName));
                        CrWaitControl waitControl = new CrWaitControl(new Action(() =>
                        {
                            ExportSingle(Path.Combine(FolderExport, fileName), IsTemplate);
                        }));
                        return;
                    }
                }
                else//导出多个文件
                {
                    System.Windows.Forms.FolderBrowserDialog folderDia = new System.Windows.Forms.FolderBrowserDialog();
                    if (folderDia.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    {
                        for (int i = 0; i < ExcelNames.Count; i++)
                        {
                            File.Copy(Path.Combine(Application.StartupPath, ExcelNames[i]), Path.Combine(folderDialog.SelectedPath, Path.GetFileName(ExcelNames[i])), true);
                            ExportSingle(Path.Combine(folderDialog.SelectedPath, Path.Combine(folderDialog.SelectedPath, Path.GetFileName(ExcelNames[i]))), false);
                        }
                        ShowMessage.Notice("导出成功!");
                    }
                    return;
                }
            }
            catch(Exception ex)
            {
                ShowMessage.Notice("操作异常" + ex.Message);
                
            }
        }

        private bool ExportSingle(string filePath,bool isTemplate=false,bool isShowExcel=true)
        {
            Dictionary<string, DataTable> sheetNames = new Dictionary<string, DataTable>();
            List<Dictionary<string, DataTable>> templateData =new List<Dictionary<string, DataTable>>();  
            
            int addCols = 0;
            BaseExcel excelOp = new BaseExcel();
            excelOp.Open(filePath);
            DataTable dt=null;

            for (int i = 0; i < templates.Count; i++)
            {
                /*----获取导出需要的数据----*/
                if (!isTemplate)
                {
                    if (GetExportDataTable == null)
                    {
                        GetExportDataTable = new ExportHandler(delegate {
                            string sql = " select * from " + templates[i].TableName;
                            return this.dbInstance.Select(sql );
                        });
                    }
                    dt = GetExportDataTable(configFileNames[i]);
                    if (dt == null || dt.Rows.Count == 0)
                    {
                        dt = TemplateData;
                        isTemplate = true;
                    }
                }
                else
                {
                    dt = TemplateData;
                } 
                
                //获取完毕
         //       if (!filePath.EndsWith(Path.GetFileName(templates[i].ExcelFileName))) continue;
                /*-------插入分析元素列-------*/

                if (templateAnalElem.ContainsKey(templates[i]))
                {
                    DataTable dtAnalItem = templateAnalElem[templates[i]];
                    if (dtAnalItem.Rows.Count == 0)
                    {
                        List<string> addedElem = new List<string>();//
                        for (int k = 0; dt != null && k < dt.Rows.Count; k++)
                        {
                            if (isTemplate)
                            {
                                /*-----如果是导出模板的话就根据给的保存分析元素的DataTable插入分析元素----*/
                                for (int j = 0; j < TemplateAnalItems.Rows.Count; j++)
                                {
                                    DataRow drItem = dtAnalItem.NewRow();
                                    drItem["ElemName"] = TemplateAnalItems.Rows[j]["ElemName"].ToString();
                                    drItem["ElemUnit"] = TemplateAnalItems.Rows[j]["ElemUnit"].ToString();
                                    dtAnalItem.Rows.Add(drItem);
                                }//
                            }
                            else
                            {
                                DataTable dtAnal = null;
                                //如果是导出数据的话,先从送样结果表查数据,没数据则从送样表里面查询出分析元素名称，然后插入到分析元素列
                                dtAnal = dbInstance.Select("select ElemName as AnalItem from G_AnalElem where SampNo='" + dt.Rows[k]["SampNo"].ToString() + "'");
                                if (dtAnal == null || dtAnal.Rows.Count == 0)
                                {
                                    dtAnal = dbInstance.Select("select AnalItem from G_SampSend where SampNo='" + dt.Rows[k]["SampNo"].ToString() + "'");
                                    if (dtAnal.Rows.Count > 0)
                                    {
                                        for (int j = 0; j < dtAnal.Rows[0]["AnalItem"].ToString().Split(';').Length; j++)
                                        {
                                            if (addedElem.Contains(dtAnal.Rows[0]["AnalItem"].ToString().Split(';')[j])) continue;
                                            addedElem.Add(dtAnal.Rows[0]["AnalItem"].ToString().Split(';')[j]);
                                            DataRow drItem = dtAnalItem.NewRow();
                                            drItem["ElemName"] = dtAnal.Rows[0]["AnalItem"].ToString().Split(';')[j];

                                            dtAnalItem.Rows.Add(drItem);
                                        }
                                    }
                                }
                                else
                                {
                                    for (int j = 0; j < dtAnal.Rows.Count; j++)
                                    {
                                        if (addedElem.Contains(dtAnal.Rows[j]["AnalItem"].ToString())) continue;
                                        addedElem.Add(dtAnal.Rows[j]["AnalItem"].ToString());
                                        DataRow drItem = dtAnalItem.NewRow();
                                        drItem["ElemName"] = dtAnal.Rows[j]["AnalItem"].ToString();
                                        dtAnalItem.Rows.Add(drItem);
                                    }
                                }
                            }

                        }
                    }
                    if (dtAnalItem.Rows.Count > AnalItemCount)
                    {
                        addCols = dtAnalItem.Rows.Count - AnalItemCount;
                        excelOp.InsertColumns(templates[i].AnalItemStartColumn.ToInt() + 1, addCols);
                    }
                    for (int j = 0; j < dtAnalItem.Rows.Count; j++)
                    {                       
                        excelOp.WriteData(dtAnalItem.Rows[j]["ElemName"].ToString() /*+ "(" + dtAnalItem.Rows[j]["ElemUnit"].ToString() + ")"*/, templates[i].RecordStartRowIndex.ToInt() - 1, templates[i].AnalItemStartColumn.ToInt() + j);
                    }
                }//分析元素列插入完毕
                /*-----------------------拷贝sheet页并且设置每个sheet页对应的数据--------------------*/
                
                Dictionary<string, DataTable> sheetData = new Dictionary<string, DataTable>();
                if (dt == null || dt.Rows.Count == 0)
                {
                    templateData.Add(sheetData);
                    continue;
                } 
                string curActiveSheetName = excelOp.GetActiveSheet();
                if (!string.IsNullOrEmpty(templates[i].SheetName))
                {
                    curActiveSheetName = templates[i].SheetName;
                    excelOp.ActivateSheet(curActiveSheetName);
                }
                if (templates[i].FixedCells!=null&&templates[i].FixedCells.Length>0 && !isTemplate)
                {
                    List<string> fixedColsName = new List<string>();
                    for (int iFixedCols = 0; iFixedCols < templates[i].FixedCells.Length; iFixedCols++)
                    {
                        if (dt.Columns.Contains(templates[i].FixedCells[iFixedCols].FieldName))
                            fixedColsName.Add(templates[i].FixedCells[iFixedCols].FieldName);
                    }
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        DataSet ds=new DataSet();
                        //if (GroupByFiexedCells)
                            ds = dt.GroupBy(fixedColsName);
                        //else
                        //{
                        //    ds.Tables.Add(dt);
                        //}
                        if (ds.Tables.Count > 0)
                        {
                            if (!sheetNames.ContainsKey(curActiveSheetName))
                            {
                                sheetNames.Add(curActiveSheetName, ds.Tables[0]);
                            }
                            for (int ids = 0; ids < ds.Tables.Count; ids++)
                            {
                                bool isFind = false;
                                foreach (string key in sheetNames.Keys)
                                {
                                    if (!string.IsNullOrEmpty(templates[i].SheetName) && !key.Contains(templates[i].SheetName))
                                    {
                                        continue;
                                    }
                                    for (int iFixedCols = 0; iFixedCols < templates[i].FixedCells.Length; iFixedCols++)
                                    {
                                        if (ds.Tables[ids].Columns.Contains(templates[i].FixedCells[iFixedCols].FieldName) && ds.Tables[ids].Rows[0][templates[i].FixedCells[iFixedCols].FieldName].ToString() != sheetNames[key].Rows[0][templates[i].FixedCells[iFixedCols].FieldName].ToString())
                                        {
                                            break;
                                        }
                                        else if (iFixedCols == templates[i].FixedCells.Length - 1)
                                        {
                                            if (templates[i].ExcelRcdRows.ToInt() == 0)
                                                sheetData.Add(key, ds.Tables[ids]);
                                            else
                                            {
                                                int copyCount = 0;
                                                if (ds.Tables[ids].Rows.Count % templates[i].ExcelRcdRows.ToInt() == 0)
                                                    copyCount = ds.Tables[ids].Rows.Count / templates[i].ExcelRcdRows.ToInt()-1;
                                                else
                                                    copyCount = ds.Tables[ids].Rows.Count / templates[i].ExcelRcdRows.ToInt();
                                                sheetData.Add(key, ds.Tables[ids].GetRows(0, templates[i].ExcelRcdRows.ToInt()));
                                                for (int iCount = 0; iCount < copyCount; iCount++)
                                                {
                                                    string sheetName = excelOp.CopySheet(key);
                                                    sheetNames.Add(sheetName, ds.Tables[ids].GetRows((iCount+1) * templates[i].ExcelRcdRows.ToInt(), templates[i].ExcelRcdRows.ToInt()));
                                                    sheetData.Add(sheetName, ds.Tables[ids].GetRows((iCount+1) * templates[i].ExcelRcdRows.ToInt(), templates[i].ExcelRcdRows.ToInt()));
                                                }
                                            }
                                            isFind = true;
                                        }
                                    }
                                    if (isFind)
                                    {
                                        break;
                                    }
                                }
                                if (!isFind )
                                {
                                    int copyCount = 0;
                                    if ( ds.Tables[ids].Rows.Count % templates[i].ExcelRcdRows.ToInt()==0)
                                        copyCount = ds.Tables[ids].Rows.Count / templates[i].ExcelRcdRows.ToInt();
                                    else
                                        copyCount = ds.Tables[ids].Rows.Count / templates[i].ExcelRcdRows.ToInt()+1;
                                    for (int iCount = 0; iCount < copyCount; iCount++)
                                    {
                                        string sheetName = excelOp.CopySheet(curActiveSheetName);
                                        sheetNames.Add(sheetName, ds.Tables[ids].GetRows(iCount * templates[i].ExcelRcdRows.ToInt(), templates[i].ExcelRcdRows.ToInt()));
                                        sheetData.Add(sheetName, ds.Tables[ids].GetRows(iCount * templates[i].ExcelRcdRows.ToInt(), templates[i].ExcelRcdRows.ToInt()));
                                    }

                                }
                            }
                        }
                    }
                }
                else
                {
                    sheetData.Add(curActiveSheetName, dt);
                }//sheet页拷贝完毕
                templateData.Add(sheetData);
            }
            Dictionary<string, int> addedCount = new Dictionary<string, int>();
            //if (!isTemplate)
            //{
            for (int i = 0; i < templates.Count && templateData.Count>i; i++)
                {
                    Dictionary<string, DataTable> sheetData = templateData[i];
               
                    
                    //if (i > 0 && templates[i].TableName == templates[i - 1].TableName) continue;
                    foreach (string sheetName in sheetData.Keys)
                    {
                          
                        excelOp.ActivateSheet(sheetName);
                        DataTable dtWrite = sheetData[sheetName];
                        if (templates[i].RcdCells != null && templates[i].RcdCells.Length != 0)
                        {
                            if (addedCount.ContainsKey(sheetName))
                            {
                                int calcNum = dtWrite.Rows.Count - templates[i].ExcelRcdRows.ToInt() - addedCount[sheetName];
                                if (calcNum > 0)
                                {
                                    excelOp.InsertRows(templates[i].RecordStartRowIndex.ToInt() + 1, calcNum);
                                    addedCount[sheetName] = addedCount[sheetName] + calcNum;
                                }
                            }
                            else
                            {
                                int calcNum = dtWrite.Rows.Count - templates[i].ExcelRcdRows.ToInt();
                                if (calcNum > 0)
                                {
                                    excelOp.InsertRows(templates[i].RecordStartRowIndex.ToInt() + 1, calcNum);
                                    addedCount.Add(sheetName, calcNum);
                                }
                            }
                        }
                        for (int j = 0; j < dtWrite.Rows.Count; j++)
                        {
                               
                            /*----将DataTable里面的数据写入到Excel文件中----*/
                            foreach (RcdCellTemplate cell in templates[i].RcdCells)
                            {
                               
                                int WriteIndex = cell.ColumnIndex.ToInt();
                                if (isAutoMerge == true)
                                {
                                    if (j > 0 && dtWrite.Columns.Contains(cell.FieldName) && dtWrite.Rows[j][mergeTagCol].ToString() == dtWrite.Rows[j - 1][mergeTagCol].ToString() && dtWrite.Rows[j][cell.FieldName].ToString() == dtWrite.Rows[j - 1][cell.FieldName].ToString())
                                    {
                                        excelOp.MergeCells(templates[i].RecordStartRowIndex.ToInt() + j, WriteIndex, templates[i].RecordStartRowIndex.ToInt() + j - 1, WriteIndex);
                                        continue;
                                    }
                                }
                                if (dtWrite.Columns.Contains(cell.FieldName))
                                {
                                    if (cell.IsRightToAnalItem.ToInt() > 0)
                                    {
                                        WriteIndex = WriteIndex + addCols;
                                    }
                                    if (cell.IsBool == "1")  //如果是布尔类型的话就打勾
                                    {
                                        if (dtWrite.Rows[j][cell.FieldName].ToBool())
                                        {
                                            if (templates[i].IsVertical.ToInt() <= 0)
                                                excelOp.WriteData(ChkCell_Right, templates[i].RecordStartRowIndex.ToInt() + j, WriteIndex);
                                            else
                                                excelOp.WriteData(ChkCell_Right, WriteIndex, templates[i].RecordStartRowIndex.ToInt() + j * colSpan);
                                        }
                                        else
                                        {
                                            if (templates[i].IsVertical.ToInt() <= 0)
                                                excelOp.WriteData("", templates[i].RecordStartRowIndex.ToInt() + j, WriteIndex);
                                            else
                                                excelOp.WriteData("", WriteIndex, templates[i].RecordStartRowIndex.ToInt() + j * colSpan);
                                        }
                                    }
                                    else //否则直接写入数据
                                    {
                                        if (dtWrite.Rows[j][cell.FieldName].IsDataTime() && dtWrite.Rows[j][cell.FieldName].ToString().Length > 8)
                                        {
                                            if (templates[i].IsVertical.ToInt() <= 0)
                                                excelOp.WriteData(dtWrite.Rows[j][cell.FieldName].ToDateTime().ToString("yyyy-MM-dd"), templates[i].RecordStartRowIndex.ToInt() + j, WriteIndex);//yyyy年MM月dd日
                                            else
                                                excelOp.WriteData(dtWrite.Rows[j][cell.FieldName].ToDateTime().ToString("yyyy-MM-dd"), WriteIndex, templates[i].RecordStartRowIndex.ToInt() + j * colSpan);//yyyy年MM月dd日
                                        }
                                        else
                                        {
                                            if (templates[i].IsVertical.ToInt() <= 0)
                                                excelOp.WriteData(dtWrite.Rows[j][cell.FieldName].ToString(), templates[i].RecordStartRowIndex.ToInt() + j, WriteIndex);
                                            else
                                                excelOp.WriteData(dtWrite.Rows[j][cell.FieldName].ToString(), WriteIndex, templates[i].RecordStartRowIndex.ToInt() + j * colSpan);

                                        }
                                    }
                                }
                         
                            }
                            if (templates[i].FixedCells != null)
                            {
                                foreach (FixedCellTemplate cell in templates[i].FixedCells)
                                {
                                    if (dtWrite.Columns.Contains(cell.FieldName))
                                    {
                                        if (cell.IsRightToAnalItem.ToInt() > 0 && cell.IsDownToRcd.ToInt() <= 0)  //在分析元素右边的话，要根据插入的分析元素列调整该单元格位置
                                        {
                                            if (cell.IsBool == "1") //布尔类型就打钩
                                            {
                                                if (dtWrite.Rows[0][cell.FieldName].ToBool())
                                                {
                                                    excelOp.WriteData(ChkCell_Right, cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt() + addCols);
                                                }
                                                else
                                                {
                                                    excelOp.WriteData("", cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt() + addCols);
                                                }
                                            }
                                            else  //否则直接写入数据,时间格式的转换为"yyyy年MM月dd日"
                                            {
                                                if (dtWrite.Rows[0][cell.FieldName].ToString().IsDataTime() && dtWrite.Rows[0][cell.FieldName].ToString().Length > 8)
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToDateTime().ToString("yyyy-MM-dd"), cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt() + addCols);//yyyy年MM月dd日
                                                }
                                                else
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToString(), cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt() + addCols);

                                                }
                                            }
                                        }
                                        else if (cell.IsRightToAnalItem.ToInt() > 0 && cell.IsDownToRcd.ToInt() > 0)
                                        {
                                            int addRows = 0;
                                            if (addedCount.ContainsKey(sheetName))
                                                addRows = addedCount[sheetName];
                                            if (cell.IsBool == "1") //布尔类型就打钩
                                            {

                                                if (dtWrite.Rows[0][cell.FieldName].ToBool())
                                                {
                                                    excelOp.WriteData(ChkCell_Right, cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt() + addCols);
                                                }
                                                else
                                                {
                                                    excelOp.WriteData("", cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt() + addCols);
                                                }
                                            }
                                            else  //否则直接写入数据,时间格式的转换为"yyyy年MM月dd日"
                                            {
                                                if (dtWrite.Rows[0][cell.FieldName].ToString().IsDataTime() && dtWrite.Rows[0][cell.FieldName].ToString().Length > 8)
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToDateTime().ToString("yyyy-MM-dd"), cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt() + addCols);//yyyy年MM月dd日
                                                }
                                                else
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToString(), cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt() + addCols);

                                                }
                                            }
                                        }
                                        else if (cell.IsRightToAnalItem.ToInt() <= 0 && cell.IsDownToRcd.ToInt() > 0)
                                        {
                                            int addRows = 0;
                                            if (addedCount.ContainsKey(sheetName))
                                                addRows = addedCount[sheetName];
                                            if (cell.IsBool == "1") //布尔类型就打钩
                                            {

                                                if (dtWrite.Rows[0][cell.FieldName].ToBool())
                                                {
                                                    excelOp.WriteData(ChkCell_Right, cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt());
                                                }
                                                else
                                                {
                                                    excelOp.WriteData("", cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt());
                                                }
                                            }
                                            else  //否则直接写入数据,时间格式的转换为"yyyy年MM月dd日"
                                            {
                                                if (dtWrite.Rows[0][cell.FieldName].ToString().IsDataTime() && dtWrite.Rows[0][cell.FieldName].ToString().Length > 8)
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToDateTime().ToString("yyyy-MM-dd"), cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt());//yyyy年MM月dd日
                                                }
                                                else
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToString(), cell.RowIndex.ToInt() + addRows, cell.ColumnIndex.ToInt());

                                                }
                                            }
                                        }
                                        else
                                        {
                                            if (cell.IsBool == "1")
                                            {
                                                if (dtWrite.Rows[0][cell.FieldName].ToBool())
                                                {
                                                    excelOp.WriteData(ChkCell_Right, cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt());
                                                }
                                                else
                                                {
                                                    excelOp.WriteData("", cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt());
                                                }
                                            }
                                            else  //否则直接写入数据,时间格式的转换为"yyyy年MM月dd日"
                                            {
                                                if (dtWrite.Rows[0][cell.FieldName].ToString().IsDataTime() && dtWrite.Rows[0][cell.FieldName].ToString().Length > 8)
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToDateTime().ToString("yyyy-MM-dd"), cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt());//yyyy年MM月dd日
                                                }
                                                else
                                                {
                                                    excelOp.WriteData(dtWrite.Rows[0][cell.FieldName].ToString(), cell.RowIndex.ToInt(), cell.ColumnIndex.ToInt());
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                            //DataTable里面数据写入完毕
                            if (OperationAfterExport.ContainsKey(templates[i]) && isTemplate == false)
                            {
                                OperationAfterExport[templates[i]](dtWrite.Rows[j]);
                            }
                                //分析元素写入完毕
                       
                            /*-----------写入分析元素--------------*/
                            if (!isTemplate)
                            {
                                if (templateAnalElem.ContainsKey(templates[i]) )//大体重分析元素-当组合的信息不为空时写入信息
                                {
                                    if (templateAnalElem.ContainsKey(templates[i]))
                                    {
                                        DataTable dtAnalItem = new DataTable();
                                        if (dtWrite.Columns.Contains("SurvNo") && !string.IsNullOrEmpty(dtWrite.Rows[j]["SurvNo"].ToString()) && dtWrite.Rows[j]["SurvNo"].ToString().IsInt())
                                        {
                                            dtAnalItem = dbInstance.Select(string.Format(" select * from {0} where SampNo='{1}' and SurvNo ={2}", analTableName[templates[i]], dtWrite.Rows[j]["SampNo"].ToString(), dtWrite.Rows[j]["SurvNo"].ToString()));
                                        }
                                        else if (dtWrite.Columns.Contains("SurvNo") && !string.IsNullOrEmpty(dtWrite.Rows[j]["SurvNo"].ToString()) )
                                        {
                                            dtAnalItem = new DataTable();
                                        }
                                        else
                                        {
                                            dtAnalItem = dbInstance.Select(analTableName[templates[i]], new string[] { "SampNo" }, new string[] { dtWrite.Rows[j]["SampNo"].ToString() });
                                        }
                                        for (int k = 0; k < templateAnalElem[templates[i]].Rows.Count; k++)
                                        {
                                            for (int m = 0; m < dtAnalItem.Rows.Count; m++)
                                            {
                                                if (dtAnalItem.Rows[m]["ElemName"].ToString() == templateAnalElem[templates[i]].Rows[k]["ElemName"].ToString())
                                                {
                                                    if (isAutoMerge == true)
                                                    {
                                                        if (j > 0 && dtWrite.Rows[j][mergeTagCol].ToString() == dtWrite.Rows[j - 1][mergeTagCol].ToString() && dtAnalItem.Rows[m]["ElemValue"].ToString() == excelOp.ReadData(templates[i].RecordStartRowIndex.ToInt() + j-1, templates[i].AnalItemStartColumn.ToInt() + k))
                                                        {
                                                            excelOp.MergeCells(templates[i].RecordStartRowIndex.ToInt() + j-1, templates[i].AnalItemStartColumn.ToInt() + k, templates[i].RecordStartRowIndex.ToInt() + j, templates[i].AnalItemStartColumn.ToInt() + k);
                                                            continue;
                                                        }
                                                    }
                                                    excelOp.WriteData(dtAnalItem.Rows[m]["ElemValue"].ToString(), templates[i].RecordStartRowIndex.ToInt() + j, templates[i].AnalItemStartColumn.ToInt() + k);
                                                    break;
                                                }
                                            }
                                        }
                                    }                                    
                                 
                                }
                            }
                        }
                    }
                }

            //}
            if (isShowExcel)
            {
                excelOp.Save();
                excelOp.ShowExcel();
            }
            if(!isShowExcel)
            {
                excelOp.Save();
                excelOp.Close();
            }
            return true;
        }
        private void ValidateData(ExcelTemplate template,DataTable dt, Dictionary<DataRow, DataTable> analItem)
        {
            //string fileName = Path.GetFileName(template.ExcelFileName);
            List<string> addedErrors = new List<string>();
            /*------------单元格验证-------------*/
            for (int i = 0; i < template.RcdCells.Length; i++)
            {
                if(FieldsValidate.ContainsKey(template.RcdCells[i]))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string errorInfo = "";
                        string[] data = rowReferToData[dt.Rows[j]];
                        if (!FieldsValidate[template.RcdCells[i]](dt.Rows[j],out errorInfo))
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                          
                            item.Text = "'"+data[0] + "'第" + data[1] + "行第" + template.RcdCells[i].ColumnIndex + "列输入有误," + errorInfo;
                           
                            if (!addedErrors.Contains(item.Text))
                            {
                                addedErrors.Add(item.Text);
                                this.ProgList.Append(item);
                            }
                        }
                    }
                }
            }
            for (int i = 0; i < template.FixedCells.Length; i++)
            {
                if (FieldsValidate.ContainsKey(template.FixedCells[i]))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string errorInfo = "";
                        string[] data = rowReferToData[dt.Rows[j]];
                        if (!FieldsValidate[template.FixedCells[i]](dt.Rows[j], out errorInfo))
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            item.Text = "'" + data[0] + "'第" + template.FixedCells[i].RowIndex+ "行第" + template.FixedCells[i].ColumnIndex + "列输入有误," + errorInfo;
                            if (!addedErrors.Contains(item.Text))
                            {
                                addedErrors.Add(item.Text);
                                this.ProgList.Append(item);
                            }
                        }
                    }

                }
            } //单元格验证
            /*------------编码规则验证-------------*/
            for (int i = 0; i < template.RcdCells.Length; i++)
            {
                if (FieldsPrefix.ContainsKey(template.RcdCells[i]))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        if (!dt.Rows[j][template.RcdCells[i].FieldName].ToString().StartsWith(FieldsPrefix[template.RcdCells[i]]) || (dt.Rows[j][template.RcdCells[i].FieldName].ToString().Replace(FieldsPrefix[template.RcdCells[i]],"")).Length>3||(dt.Rows[j][template.RcdCells[i].FieldName].ToString().Replace(FieldsPrefix[template.RcdCells[i]],"")).Length<1||!(dt.Rows[j][template.RcdCells[i].FieldName].ToString().Replace(FieldsPrefix[template.RcdCells[i]],"")).IsInt())
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            string[] data = rowReferToData[dt.Rows[j]];
                            if(template.IsVertical.ToInt()<=0)
                                item.Text = "'"+data[0] + "'第" + data[1] + "行第" + template.RcdCells[j].ColumnIndex + "列输入有误,应为'" + FieldsPrefix[template.RcdCells[i]] + "'加1到3位顺序号";
                            else
                                item.Text = "'" + data[0] + "'第" + template.RcdCells[j].ColumnIndex + "行第" + (template.RecordStartRowIndex.ToInt() + j).ToString() + "列输入有误,应为'" + FieldsPrefix[template.RcdCells[i]] + "'加1到3位顺序号";

                            this.ProgList.Append(item);
                        }
                    }
                }
            }
            for (int i = 0; i < template.FixedCells.Length; i++)
            {
                if (FieldsPrefix.ContainsKey(template.FixedCells[i]))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string[] data = rowReferToData[dt.Rows[j]];
                        if (!dt.Rows[j][template.FixedCells[i].FieldName].ToString().StartsWith(FieldsPrefix[template.FixedCells[i]]) || (dt.Rows[j][template.FixedCells[i].FieldName].ToString().Replace(FieldsPrefix[template.FixedCells[i]], "")).Length > 3 || (dt.Rows[j][template.FixedCells[i].FieldName].ToString().Replace(FieldsPrefix[template.FixedCells[i]], "")).Length < 1 || !(dt.Rows[j][template.FixedCells[i].FieldName].ToString().Replace(FieldsPrefix[template.FixedCells[i]], "")).IsInt())
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;

                            item.Text = "'" + data[0] + "'第" + template.FixedCells[i].RowIndex + "行第" + template.FixedCells[i].ColumnIndex + "列输入有误,应为'" + FieldsPrefix[template.FixedCells[i]] + "'加1到3位顺序号";
                            this.ProgList.Append(item);
                        }
                    }

                }
            } //单元格验证
            //验证通过接口添加进的验证方法
            if (commonValidates.ContainsKey(template))
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (nullRows.Contains(dt.Rows[i])) continue;
                    string[] data = rowReferToData[dt.Rows[i]];
                    for (int j = 0; j < commonValidates[template].Count; j++)
                    {
                        string res = commonValidates[template][j](dt.Rows[i]);
                        if (!string.IsNullOrEmpty(res))
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            if (template.RecordStartRowIndex.ToInt()!=0)
                                item.Text = "'" + data[0] + "'第" + data[1] + "行输入有误:" + res;
                            else
                                item.Text = res;
                            this.ProgList.Append(item);
                        }
                    }
                   
                }
                    
            }
            /*--------验证主外键-----*/
            for (int i = 0; i < template.RcdCells.Length; i++)
            {
                if (!string.IsNullOrEmpty(template.RcdCells[i].BindTableName))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string[] data = rowReferToData[dt.Rows[j]];
                        if (template.RcdCells[i].IsBGD.ToInt() > 0)
                        {
                            if (!dbInstance.IsExist(template.RcdCells[i].BindTableName, template.RcdCells[i].BindFieldName, dt.Rows[j][template.RcdCells[i].FieldName].ToString()))
                            {
                                IsValidateSuccess = false;
                                ProgressListItem item = new ProgressListItem();
                                item.ImageType = ProgImageTypeEm.Error;
                                if(template.IsVertical.ToInt()<=0)
                                    item.Text = "'"+data[0] + "'第" + data[1] + "行第" + template.RcdCells[j].ColumnIndex + "列输入有误," + template.RcdCells[i].BindTableChineseName + "中不存在编号为" + dt.Rows[j][template.RcdCells[i].FieldName].ToString() + "的记录";
                                else
                                    item.Text = "'" + data[0] + "'第" + template.RcdCells[j].ColumnIndex + "行第" + (template.RecordStartRowIndex.ToInt() + j).ToString() + "列输入有误," + template.RcdCells[i].BindTableChineseName + "中不存在编号为" + dt.Rows[j][template.RcdCells[i].FieldName].ToString() + "的记录";
                                this.ProgList.Append(item);
                            }
                        }
                    }
                }  
            }
            for (int i = 0; i < template.FixedCells.Length; i++)
            {
                if (!string.IsNullOrEmpty(template.FixedCells[i].BindTableName))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string[] data = rowReferToData[dt.Rows[j]];
                        if (template.FixedCells[i].IsBGD.ToInt() > 0)
                        {
                            if (!dbInstance.IsExist(template.FixedCells[i].BindTableName, template.FixedCells[i].BindFieldName, dt.Rows[j][template.FixedCells[i].FieldName].ToString()))
                            {
                                IsValidateSuccess = false;
                                ProgressListItem item = new ProgressListItem();
                                item.ImageType = ProgImageTypeEm.Error;
                                item.Text = "'" + data[0] + "'第" + template.FixedCells[i].RowIndex + "行第" + template.FixedCells[i].ColumnIndex + "列输入有误," + template.FixedCells[i].BindTableChineseName + "中不存在编号为" + dt.Rows[j][template.FixedCells[i].FieldName].ToString() + "的记录";
                                this.ProgList.Append(item);
                            }
                        }
                    }
                }
            }
            //住外键验证完毕
            /*-----------------验证不能为空-----------------------*/
            DataTable dtInfo = dbInstance.GetTableInfo(template.TableName);
            DataTable dtOri = dbInstance.GetTableColumns(template.TableName);
            for (int i = 0; i < template.RcdCells.Length; i++)
            {
                DataRow dr=null;
                for (int k = 0; k < dtInfo.Rows.Count; k++)
                {
                    if (dtInfo.Rows[k]["Column_Name"].ToString().ToLower() ==template.RcdCells[i].FieldName.ToLower())
                    {
                        dr = dtInfo.Rows[k];
                        break;
                    }
                }
                if (template.RcdCells[i].AllowNull.ToInt() <= 0 ||(dr!=null && dr["Nullable"].ToString()=="N"))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string[] data = rowReferToData[dt.Rows[j]];
                        if (string.IsNullOrEmpty(dt.Rows[j][template.RcdCells[i].FieldName].ToString()))
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            if (template.IsVertical.ToInt() <= 0)
                                item.Text = "'" + data[0] + "'第" + data[1] + "行第" + template.RcdCells[i].ColumnIndex + "列不能为空";
                            else
                                item.Text = "'" + data[0] + "'第" + template.RcdCells[i].ColumnIndex + "行第" + (template.RecordStartRowIndex.ToInt() + j).ToString() + "列不能为空";

                            this.ProgList.Append(item);
                        }
                    }
                }
            }
            for (int i = 0; i < template.FixedCells.Length; i++)
            {
                if (template.FixedCells[i].AllowNull.ToInt() <= 0)
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string[] data = rowReferToData[dt.Rows[j]];
                        if (string.IsNullOrEmpty(dt.Rows[j][template.FixedCells[i].FieldName].ToString()))
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            item.Text = "'" + data[0] + "'第" + template.FixedCells[i].RowIndex + "行第" + template.FixedCells[i].ColumnIndex + "列不能为空";
                            this.ProgList.Append(item);
                        }
                    }

                }
            }
            //非空验证完毕
            /*----------------------验证数据类型以及字符串最大长度---------------------------*/

            List<string> addedErr = new List<string>();
            for (int i = 0; i < dtOri.Columns.Count; i++)
            {
                if (dt.Columns.Contains(dtOri.Columns[i].ToString()))
                {
                    for (int j = 0; j < dt.Rows.Count; j++)
                    {
                        if (nullRows.Contains(dt.Rows[j])) continue;
                        string[] data = rowReferToData[dt.Rows[j]];
                        /*-----------验证数据类型-----------*/
                        if (!dt.Rows[j][dtOri.Columns[i].ToString()].IsDataType(dtOri.Columns[i].DataType))
                        {
                        
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            object obj = template.FindCellByFieldName(dtOri.Columns[i].ToString());
                            if (obj is FixedCellTemplate)
                            {
                                item.Text = "'" + data[0] + "'第" + (obj as FixedCellTemplate).RowIndex+ "行第" + (obj as FixedCellTemplate).ColumnIndex + "列应为" + dtOri.Columns[i].DataType.ToChinese();
                            }
                            else if (obj is RcdCellTemplate)
                            {
                                if (template.IsVertical.ToInt() <= 0)
                                    item.Text = "'" + data[0] + "'第" + data[1] + "行第" + (obj as RcdCellTemplate).ColumnIndex + "列应为" + dtOri.Columns[i].DataType.ToChinese();
                                else
                                    item.Text = "'" + data[0] + "'第" + (obj as RcdCellTemplate).ColumnIndex + "行第" + (template.RecordStartRowIndex.ToInt() + j).ToString() + "列应为" + dtOri.Columns[i].DataType.ToChinese();

                            }
                            if (!addedErr.Contains(item.Text))
                            {
                                this.ProgList.Append(item);
                                addedErr.Add(item.Text);
                            }
                        }  //  数据类型验证完毕
                        /*----------------验证数字的最大最小值----------------------*/
                        if (dtOri.Columns[i].DataType == typeof(System.Int32) || dtOri.Columns[i].DataType == typeof(System.Double))
                        {
                            if (dt.Rows[j][dtOri.Columns[i].ToString()].ToDouble() > maxValue || dt.Rows[j][dtOri.Columns[i].ToString()].ToDouble() < minValue)
                            {
                                IsValidateSuccess = false;
                                ProgressListItem item = new ProgressListItem();
                                item.ImageType = ProgImageTypeEm.Error;
                                object obj = template.FindCellByFieldName(dtOri.Columns[i].ToString());
                                if (obj is FixedCellTemplate)
                                {
                                    item.Text = "'" + data[0] + "'第" + (obj as FixedCellTemplate).RowIndex + "行第" + (obj as FixedCellTemplate).ColumnIndex + "列输入错误，应在" + minValue.ToString() + "到" + maxValue.ToString() + "之间。";
                                }
                                else if (obj is RcdCellTemplate)
                                {
                                    if (template.IsVertical.ToInt() <= 0)
                                        item.Text = "'" + data[0] + "'第" + data[1] + "行第" + (obj as RcdCellTemplate).ColumnIndex + "列输入错误，应在" + minValue.ToString() + "到" + maxValue.ToString() + "之间。";
                                    else
                                        item.Text = "'" + data[0] + "'第" + data[1] + "行第" + (template.RecordStartRowIndex.ToInt() + j).ToString() + "列输入错误，应在" + minValue.ToString() + "到" + maxValue.ToString() + "之间。";

                                }
                                if (!addedErr.Contains(item.Text))
                                {
                                    this.ProgList.Append(item);
                                    addedErr.Add(item.Text);
                                }
                            }
                        }//最大最小值验证完毕
                        /*----------验证字符串长度---------------*/

                        DataRow dr=null;
                        for (int k = 0; k < dtInfo.Rows.Count; k++)
                        {
                            if (dtInfo.Rows[k]["Column_Name"].ToString() == dtOri.Columns[i].ColumnName)
                            {
                                dr = dtInfo.Rows[k];
                                break;
                            }
                        }
                        if (dr == null)
                        { 
                        }
                        if (dtOri.Columns[i].DataType == typeof(string) && dr!=null && dt.Rows[j][dtOri.Columns[i].ToString()].ToString().Length > dr["DATA_LENGTH"].ToInt() && dr["DATA_LENGTH"].ToInt() != 0)
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            object obj = template.FindCellByFieldName(dtOri.Columns[i].ToString());
                            if (obj is FixedCellTemplate)
                            {
                                item.Text = "'" + data[0] + "'第" + (obj as FixedCellTemplate).RowIndex + "行第" + (obj as FixedCellTemplate).ColumnIndex + "列数据过长,最大长度为" + dr["DATA_LENGTH"].ToInt() + "个字符";
                            }
                            else if (obj is RcdCellTemplate)
                            {
                                if (template.IsVertical.ToInt() <= 0)
                                    item.Text = "'" + data[0] + "'第" + data[1] + "行第" + (obj as RcdCellTemplate).ColumnIndex + "列数据过长,最大长度为" + dr["DATA_LENGTH"].ToInt() + "个字符";
                                else
                                    item.Text = "'" + data[0] + "'第" + data[1] + "行第" + (template.RecordStartRowIndex.ToInt() + j).ToString() + "列数据过长,最大长度为" + dr["DATA_LENGTH"].ToInt() + "个字符";
                            }

                            this.ProgList.Append(item);
                        } //字符串长度验证完毕
                    }
                }
            }
            /*-------------------验证分析元素------------------------*/
            if (analItem != null)
            {
                int rowIndex = 0;
                foreach ( DataRow drItem in analItem.Keys)
                {
                    if (nullRows.Contains(drItem)) continue;
                    string[] data = rowReferToData[drItem];
                    for (int i = 0; i < analItem[drItem].Rows.Count; i++)
                    {
                        if (!analItem[drItem].Rows[i]["ElemValue"].IsDouble())
                        {
                            IsValidateSuccess = false;
                            ProgressListItem item = new ProgressListItem();
                            item.ImageType = ProgImageTypeEm.Error;
                            item.Text = "'" + data[0] + "'第" + data[1] + "行第" + (template.AnalItemStartColumn.ToInt() + i).ToString() + "列应为数字类型";
                            this.ProgList.Append(item);
                        }
                    }
                    rowIndex++;
                }
            }
            //数据类型验证完毕
        }
        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="newFileName">导出后的配置文件名字</param>
        /// <returns></returns>
        public void ExportTemplate()
        {
             ExportData(true);
        }
    }
}
